import time
import os
import shutil
import pandas as pd
from pandas import DataFrame
from tqdm import tqdm
import openpyxl

colNames = ["监管企业名称", "债权人（实际债权人名称）", "债务人", "债务人类型", "会计科目", "款项分类",
            "债权余额", "是否逾期", "账龄", "账龄分类", "形成原因", "所属地区", "备注",
            "债务单位是否确认（是/否）", "交投集团二级公司名称（债权人）",
            "项目类型", "项目名称"]

zoneNames = ['自治区',
             '乌鲁木齐市',
             '伊犁州',
             '昌吉州',
             '博州',
             '巴州',
             '喀什地区',
             '阿克苏地区',
             '和田地区',
             '阿勒泰地区',
             '塔城地区',
             '克拉玛依市',
             '吐鲁番市',
             '哈密市',
             '克州']


def cal_sum_and_desc(data_frame):
    """
    根据输入记录汇总
    :param data_frame: panda格式的数据
    :return: （sum, desc） sum为金额，desc为分项目描述信息
    """
    if data_frame is None or data_frame.size == 0:
        return 0, ""
    typeName = data_frame['款项分类'].unique()
    sum = data_frame['债权余额'].sum()
    desc = "应收项目" + "、".join(typeName) + ("%.2f" % sum) + "万元。"

    projMoneyList = []
    for row in data_frame[['形成原因', '债权余额']].itertuples():
        # print(row,type(row))
        projMoneyList.append(row[1] + ("%.2f" % row[2]) + "万元")

    desc += ";".join(projMoneyList)
    desc += "。"
    # print(desc)
    return (sum, desc)


if __name__ == '__main__':

    # 模板文件存放路径
    path = "./excels/"

    files = os.listdir(path)

    print("扫描到以下%d个待汇总文件：" % (len(files)))
    print("================")
    for file in files:
        print(file)
    print("================")
    data = []

    for file in files:

        print("读取文件 %s" % (path + file))
        workbook = openpyxl.load_workbook(path + file)
        sheet = workbook['附件1  应收地方政府类债权（包含其他省份地方政府）']

        # 开始读取的行数，对应excel文件的第一条数据记录
        row = 5

        # 获取“序号”
        indexValue = sheet['A5'].value
        while indexValue != None:
            # print(indexValue)
            # 记录每行记录
            record = []
            for i in range(17):
                loc = chr(ord('B') + i) + str(row)
                record.append(sheet[loc].value)
            # print(record)
            data.append(record)

            row += 1;
            indexValue = sheet['A' + str(row)].value
        print("扫描到%d条记录" % (row - 5))

    # 将数据写入汇总表
    workbook = openpyxl.load_workbook("template/out.xlsx")
    sheet = workbook['附件1  应收地方政府类债权（包含其他省份地方政府）']
    sheet.insert_rows(5, len(data) - 1)

    # print(sheet['A1'].value)

    print("所有数据输出至汇总表：附件1  应收地方政府类债权（包含其他省份地方政府）")
    startRow = 5
    for rowIndex, record in enumerate(tqdm(data)):
        sheet['A' + str(startRow + rowIndex)] = rowIndex + 1

        for col, item in enumerate(record):
            loc = chr(ord('B') + col) + str(startRow + rowIndex)
            sheet[loc].value = item

    df = pd.DataFrame(data)

    df.columns = colNames
    df['所属地区'] = df["所属地区"].str.strip()

    zoneGroups = df.groupby(["所属地区", "会计科目"])

    groupKeys = zoneGroups.groups.keys()
    # print()
    # print(zoneGroups.keys)

    # print( zoneGroups.get_group(('伊犁地区', '其他应收款1')))

    print("汇总地州信息")

    # 保存汇总结果
    zoneResult = {}
    for zone in tqdm(zoneNames):

        # 每个地州的金额和描述
        res = {"sum": 0, "desc": ""}
        # print(zone)

        sum1 = 0
        sum2 = 0

        type1Group = pd.DataFrame()

        if (zone, "应收账款") in groupKeys:
            curGroupItems = zoneGroups.get_group((zone, "应收账款"))

            type1Group = pd.DataFrame(curGroupItems)
        if (zone, "合同资产") in groupKeys:
            type1Group = pd.concat([type1Group, pd.DataFrame(zoneGroups.get_group((zone, "合同资产")))])

        sum1, desc1 = cal_sum_and_desc(type1Group)
        # print(curGroupItems)
        if desc1 != "":
            res["desc"] = "一、" + desc1

        if (zone, "其他应收款") in groupKeys:
            curGroupItems = zoneGroups.get_group((zone, "其他应收款"))
            sum2, desc2 = cal_sum_and_desc(curGroupItems)
            # print(curGroupItems)

            if res["desc"] == "":
                res["desc"] = "一、" + desc2
            else:
                res["desc"] = res["desc"] + "         二、" + desc2

        res['sum'] = sum1 + sum2
        zoneResult[zone] = res

    # print(zoneResult)

    sheet = workbook['汇总表']

    for i, zone in enumerate(tqdm(zoneNames)):
        if zone in zoneResult:
            sheet['C' + str(5 + i)] = "%.2f" % zoneResult[zone]["sum"]
            sheet['E' + str(5 + i)] = zoneResult[zone]["desc"]
            # print(zoneResult[zone])

    # 保存最终xlsx文件
    outfile_suffix = time.strftime("%Y%m%d%H%M%S", time.localtime())
    outfile = "汇总表" + outfile_suffix + ".xlsx"
    workbook.save(outfile)

    input("汇总完成，按任意键退出")
